var express = require('express');
var port = process.env.PORT || 3000;
var path = require('path');
var app = express();
var http = require('http');
var url = require("url");
var mysql      = require('mysql');
var bodyParser = require('body-parser');


app.set('views','./views');
app.set('view engine','jade');

//解析url
app.use(bodyParser.urlencoded({
	extended: true
}));
app.use(express.static(path.join(__dirname, 'public')));
app.listen(port);

var connection = mysql.createConnection({
  host     : 'localhost',
  user     : 'root',
  password : 'root',
  database : 'test'
});
 
connection.connect();

app.get('/',function(req,res){
	//渲染首页数据
	connection.query('select * from goods', function (error, results1, fields) {
		var data = results1;
		var len = data.length;
		var array = [];
		var array2 = [];
		data.forEach(function(e1,e1_index){
			//根据gid查询----begin
			connection.query('select * from property where gid='+e1.id+'',function(error, results2, fields){
				if(results2){
					array2 = [];
					results2.forEach(function(e2){
						array2.push({
							'id':e2.id,
							'gid':e2.gid,
							'spec':e2.spec,
							'num':e2.num
						});
					})
					array.push({
						'name':e1.name,
						'property':array2
					});

					//检测到数据装载完成，渲染数据
					if((e1_index+1) == len){
							res.render('index',{
								'title':'首页',
								 data:array
							});
							res.end();
					}
				}
			})
			//根据gid查询----end
		})
		
	});

})


//增加整个一条数据
app.post('/add',function(req,res){
	var name = req.body.name,
			spec = req.body.spec,
			num = req.body.num;

	//第一次添加到名称表
	connection.query(`insert into goods(name)values("${name}")`,function(error,results1,f){
		if(results1){
			//第二次添加到属性表
			connection.query(`insert into property(gid,spec,num)values("${results1.insertId}","${spec}","${num}")`,function(error,results2,f){
				if(results2){
					res.redirect('/');
				}else{
					console.log(error);
				}
			})
		}else{
			console.log(error);
		}
	})
})

//增加单独一条数据
app.post('/add1',function(req,res){
	var name = req.body.name,
			spec = req.body.spec,
			num = req.body.num;
			gid = req.body.gid;
	//添加到属性表
	connection.query(`insert into property(gid,spec,num)values("${gid}","${spec}","${num}")`,function(error,results2,f){
		if(results2){
			res.redirect('/');
		}else{
			console.log(error);
		}
	})
})

//修改库存数据
app.post('/atrData',function(req,res){
	var deleteId = req.body.deleteId,
			leftNum = req.body.leftNum;
	connection.query(`update property set num="${leftNum}" where id="${deleteId}"`,function(error,results,f){
		if(results){
			res.redirect('/');
		}else{
			console.log(error);
		}
	})
})

//删除该条数据
app.post('/deleteData',function(req,res){
	var deleteId = req.body.deleteLine;
	var gid = req.body.gid;
	connection.query(`delete from property where id="${deleteId}"`,function(error,results,f){
		if(results){
			connection.query(`select * from property where gid="${gid}"`,function(error,results2,f){
				if(results2){
					if(results2.length == 0){
						connection.query(`delete from goods where id="${gid}"`,function(error,results3,f){
							console.log(results3);
						})
					}
					res.redirect('/');
				}
			})
		}else{
			console.log(error);
		}
	})
})